﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using MySql.Data;
using MySql.Data.MySqlClient;
using ConfigLibrary;
using System.IO;
using System.Runtime.Serialization.Formatters.Binary;
using System.Windows.Forms;

namespace Contacts
{

    public class MySqlConn
    {
        #region Globals
        //commands and adapters are placed at the top to use throughtout the class
        public DataSet dscontacts = new DataSet();
        private DataSet compareds = new DataSet();       
        public DataTable original;
        public DataSet currdata = new DataSet();
        public compare compform = new compare();
        private DataGridView datagv = new DataGridView();
        public MySqlConnection conn;
        MySqlCommand cmd;
        MySqlCommand cmdup = new MySqlCommand();
        MySqlCommand compcmd = new MySqlCommand();
        MySqlCommand contcmd = new MySqlCommand();
        MySqlCommand contcmdi = new MySqlCommand();        
        MySqlCommand pcontcmd = new MySqlCommand();
        MySqlDataAdapter da;
        MySqlDataAdapter daup = new MySqlDataAdapter();
        MySqlDataAdapter compda = new MySqlDataAdapter();
        MySqlDataAdapter contda = new MySqlDataAdapter();        
        MySqlDataAdapter pcontda = new MySqlDataAdapter();        
        DataRow updatedrow;
        DataRow uploadfixrow;
        DataSet contcomp = new DataSet();
        DataGridViewCell str = new DataGridViewTextBoxCell();
        bool ts;       
        public string top;
               
        // one sqlcommandbuilder per adapter
        // I hope this makes it easy to pick 
        // which tables to update       
        MySqlCommandBuilder _compbuilder;        
        MySqlCommandBuilder _pcontbuilder;        
        MySqlCommandBuilder _contbuilder;
        

        // Config info
        private Config config;
        private string _username;
        private string _host;
        private string _pass;
        private string _database;
        private int rc;
       
        public DataSet _changed_data;
        private DataSet _original_data = new DataSet();
        private string _sql;
        private SqlBuilder builder;
        #endregion

        #region CTOR
        /// <summary>
        /// Default constructor.  Initializes the config file
        /// </summary>
        public MySqlConn()
        {
            //loads the config file
            try
            {

                //this switches between the mysql contacts and mysql test databases
//#if (DEBUG)

                Stream s = File.Open(Properties.Settings.Default.CONFIGPATHTEST, FileMode.Open);
                //Stream s = File.Open(Properties.Settings.Default.CONFIGPATH, FileMode.Open);

//#elif (RELEASE)
                 //Stream s = File.Open(Properties.Settings.Default.CONFIGPATH, FileMode.Open);
//#endif

                BinaryFormatter b = new BinaryFormatter();
                config = (Config)b.Deserialize(s);
                s.Close();
                config.GetSqlInfo(out _host, out _username, out _pass, out _database);
            }
            catch (Exception ex)
            {
                mail("Config File", ex);
                MessageBox.Show("Please see Doug.  The Contacts data didn't load correctly.");
                throw;
            }
            //end load
        }

        /// <summary>
        /// Constructor for saving Data into Mysql
        /// </summary>
        /// <param name="Data">The System.Data.DataSet to save</param>
        public MySqlConn(DataSet Data, DataTable Table)
        {
            string table = Table.TableName;
            
            switch (table)
            {
                case "companies":
                    {
                        _compbuilder = new MySqlCommandBuilder(compda);
                    }
                    break;
                case "contacts":
                    {
                        _contbuilder = new MySqlCommandBuilder(contda);
                    }
                    break;
                case "project_contacts":
                    {
                        _pcontbuilder = new MySqlCommandBuilder(pcontda);
                    }
                    break;
            }
            
            // Firstly we need to split out the changed data from the dataset
            
            _original_data = Data;
            _changed_data = _original_data.GetChanges();
            //if (_changed_data.HasErrors) throw new InvalidDataException("Data has Errors.");
            builder = new SqlBuilder(_original_data);
            builder.BuilderCompleted += new SqlBuilder.SqlBuilderCompleted(builder_BuilderCompleted);
        }

        #endregion

        #region Events

        void builder_BuilderCompleted(string SqlStatement)
        {
            _sql += SqlStatement;
            StatmentCompleted(_sql);
        }

        // public event raised when the statement has been constructed
        public delegate void SaveStatementComplete(string SqlStatement);
        public event SaveStatementComplete StatmentCompleted;

        #endregion

        #region Public Properties

        public DataSet Data
        { get { return dscontacts; } }

        #endregion

        #region Public Procedures

        /// <summary>
        /// Connects to the MySql server using the database, host, and logon credentials from the config file
        /// </summary>
        public void Connect()
        {
            //this establishes sql statements for the connection for the mysql contacts database
            //and the tables in the mysql contacts database            
            try
            {
                conn = new MySqlConnection();
                conn.ConnectionString = "Database=" + _database + ";Data Source=" + _host + ";User Id=" + _username + ";Password=" + _pass;
                conn.Open();
                GC.KeepAlive(conn);
            }
            catch (Exception ex)
            {
                throw new MySqlException("Open Connection failed", ex);
            }
        }

        /// <summary>
        /// this establishes adapter for the mysql table catergories
        /// adds table and columns
        /// </summary>
        
        //this loads the the contacts database from mysql

        public void LoadContacts()
        {
            string[] commands = new string[] {"Select * from categories order by category","Select * from companies order by company_name",
                "Select * from contacts","Select * from job_roles order by job_role","Select * from project_contacts",
                "Select * from projects order by str_city"};
            string[] tables = new string[] { "categories", "companies", "contacts", "job_roles", "project_contacts", "projects" };
            int t = commands.Length;            
           
                for (int i = 0; i < t; i++)
                {
                    cmd = new MySqlCommand();
                    cmd.CommandText = commands[i];                    
                    cmd.Connection = conn;
                    da = new MySqlDataAdapter();
                    da.SelectCommand = cmd;
                    da.TableMappings.Add(tables[i], tables[i]);
                    da.FillSchema(dscontacts, SchemaType.Source, tables[i]);
                    da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                    da.Fill(dscontacts, tables[i]);
                    cmd.ExecuteNonQuery();                    
                }               
        }

        public void LoadTable(string tab, ref DataTable currtab)
        {
            cmdup.CommandText = "Select * from " + tab + "";
            cmdup.Connection = conn;
            daup.SelectCommand = cmdup;
            daup.TableMappings.Add(tab, tab);
            daup.FillSchema(currdata, SchemaType.Source, tab);
            daup.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            daup.Fill(currdata, tab);
            cmdup.ExecuteNonQuery();
            currtab = currdata.Tables[tab];
        }

        public void LoadCompaniesComp(int comp_id, ref DataTable com)
        {
            compcmd.CommandText = "Select * from companies where company_id =" + comp_id + "";
            compcmd.Connection = conn;
            compda.SelectCommand = compcmd;
            compda.TableMappings.Add("Table", "companies");
            compda.FillSchema(contcomp, SchemaType.Source, "companies");
            compda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            compda.Fill(contcomp, "companies");
            compcmd.ExecuteNonQuery();
            com = contcomp.Tables["companies"];
        }        

        public void LoadContactsComp(int cont_id, ref DataTable con)
        {                
                contcmd.CommandText = "Select * from contacts where contact_id =" + cont_id + "";
                contcmd.Connection = conn;
                contda.SelectCommand = contcmd;
                contda.TableMappings.Add("Table", "contacts");                
                contda.FillSchema(contcomp, SchemaType.Source, "contacts");                 
                contda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                contda.Fill(contcomp, "contacts");                
                contcmd.ExecuteNonQuery();
                con = contcomp.Tables["contacts"];
        }      

        public void LoadProjectContactsComp(ref DataTable pc)
        {
            //this establishes adapter for the mysql table project contacts
            //adds tables and columns

            pcontcmd.CommandText = "Select * from project_contacts";
            pcontcmd.Connection = conn;
            pcontda.SelectCommand = pcontcmd;
            pcontda.TableMappings.Add("Table", "project_contacts");
            pcontda.FillSchema(contcomp, SchemaType.Source, "project_contacts");
            pcontda.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            pcontda.Fill(contcomp, "project_contacts");
            pcontcmd.ExecuteNonQuery();
            pc = contcomp.Tables["project_contacts"];
            pcontcmd.Dispose();
            pcontda.Dispose();
        }

        //this deletes any selected row in the datagridview for deletion
        public void DeleteProjectContacts(int pcid, int strid)
        {
            pcontcmd.CommandText = "DELETE from " + _database + " .project_contacts WHERE project_contact_id = '" + pcid + "'";
            pcontcmd.Connection = conn;
            pcontda.DeleteCommand = pcontcmd;
            func.FillFolders(dscontacts, datagv, strid);
            pcontcmd.ExecuteNonQuery();
        }       

        /// <summary>
        /// Hard codes relationships between the data tables
        /// </summary>
        public void Relations()
        {
            //this establishes relationship between categories and companies

            DataColumn colparentcatcomp = dscontacts.Tables["categories"].Columns["category_id"];
            DataColumn colchildcatcomp = dscontacts.Tables["companies"].Columns["category_id"];
            DataRelation relcatcomp = new DataRelation("relcat2comp", colparentcatcomp, colchildcatcomp);
            dscontacts.Relations.Add(relcatcomp);

            //this establishes relationship between companies and contacts

            DataColumn colparentcompcont = dscontacts.Tables["companies"].Columns["company_id"];
            DataColumn colchildcompcont = dscontacts.Tables["contacts"].Columns["company_id"];
            DataRelation relcompcont = new DataRelation("relcomp2cont", colparentcompcont, colchildcompcont);
            dscontacts.Relations.Add(relcompcont);


            //this establishes relationship between projects and project contacts

            DataColumn colparentprojpc = dscontacts.Tables["projects"].Columns["str_id"];
            DataColumn colchildprojpc = dscontacts.Tables["project_contacts"].Columns["str_id"];
            DataRelation relprojpc = new DataRelation("relproj2pc", colparentprojpc, colchildprojpc);
            dscontacts.Relations.Add(relprojpc);

            //this establishes relationship between job roles and project contacts

            DataColumn colparentjrpc = dscontacts.Tables["job_roles"].Columns["job_role_id"];
            DataColumn colchildjrpc = dscontacts.Tables["project_contacts"].Columns["job_role_id"];
            DataRelation reljrpc = new DataRelation("reljr2pc", colparentjrpc, colchildjrpc);
            dscontacts.Relations.Add(reljrpc);

            //this establishes relationship between contacts and project contacts

            DataColumn colparentcontpc = dscontacts.Tables["contacts"].Columns["contact_id"];
            DataColumn colchildcontpc = dscontacts.Tables["project_contacts"].Columns["contact_id"];
            DataRelation relcontpc = new DataRelation("relcont2pc", colparentcontpc, colchildcontpc);
            dscontacts.Relations.Add(relcontpc);
        }

        public void Disconnect()
        {
            try
            {
                conn.Close();                
            }
            catch
            {

            }
        }

        /// <summary>
        /// Merge changed data into the original data. Only needed if the Data Sets have errors in them.
        /// </summary>
        public void MergeSets()
        {
            foreach (DataTable table in _changed_data.Tables)
            {
                if (table.HasErrors)
                {
                    throw new InvalidDataException(string.Format("Error in {0}", table));
                }
            }
        }

        /// <summary>
        /// Call this after you've created your SQL connection object for saving
        /// </summary>
        public void MysqlPreSave(DataSet Data, DataTable Table, DataGridView datagrv, DataTable pull, DataTable curr, DataTable user, int contcompid, string timeofpull)
        {
            string table = Table.TableName;
            _original_data = Data;                       
            top = timeofpull;
            if (table == "project_contacts")
            {
                if (Data.HasChanges(DataRowState.Added | DataRowState.Modified))
                { _changed_data = _original_data.GetChanges(); }
            }
            
            //this is the workhorse to update mysql
            switch (table)
            {
                case "companies":
                    {                        
                        contactcompare(pull, curr, user, contcompid);
                        fixdata(pull);
                        if (func.button == "b" || func.button == "c" || ts == true)
                        { updatedata(user); }
                        _changed_data = _original_data.GetChanges();
                        _compbuilder = new MySqlCommandBuilder(compda);
                        compcmd.CommandText = "SELECT * FROM " + _database + " .companies";
                        compcmd.Connection = conn;
                        compda.SelectCommand = compcmd;                        
                        compda.Update(_changed_data);
                        _changed_data.AcceptChanges();
                        contcomp.Dispose();
                    }
                    break;
                case "contacts":
                    {
                        contactcompare(pull, curr, user, contcompid);                       
                        fixdata(pull);
                        if (func.button == "b" || func.button == "c" || ts == true)
                        { updatedata(user); }                        
                        _changed_data = _original_data.GetChanges();
                        _contbuilder = new MySqlCommandBuilder(contda);
                        contcmd.CommandText = "SELECT * FROM " + _database + " .contacts";
                        contcmd.Connection = conn;
                        contda.SelectCommand = contcmd;
                        contda.Update(_changed_data);
                        _changed_data.AcceptChanges();
                        contcomp.Dispose();
                    }
                    break;
                case "project_contacts":
                    {                     
                        _pcontbuilder = new MySqlCommandBuilder(pcontda);
                        pcontcmd.CommandText = "SELECT * FROM " + _database + " .project_contacts";
                        pcontcmd.Connection = conn;
                        pcontda.SelectCommand = pcontcmd;
                        pcontda.Update(_changed_data, "project_contacts");                       
                        Data.AcceptChanges();
                        contcomp.Dispose();
                    }
                    break;
            }

            if (datagrv != null)
            {
                func.FillRows(_original_data, datagrv);
            }
                   
        }

        public void contactcompare(DataTable pull, DataTable current, DataTable user, int pos)
        {
            //original = _original_data.Tables["contacts"];            
            //int ccount = latest.Rows.Count;            
            //pull = original.Rows.Find(pos);            
            //DataRow urow = _changed_data.Tables["contacts"].Rows.Find(pos);
            //DataRow urow = user;
            ts = String.Equals(pull.Rows[0]["timestamp"].ToString(), current.Rows[0]["timestamp"]);
            if (ts == false)
            {
                compform = new compare(pull, current, user, _original_data, top, _changed_data);
                compform.ShowDialog();
                return;
            }
        }

        public void fixdata(DataTable table)
        {
            string tn = table.Columns[0].ColumnName;
            switch (tn)
            {
                case "contact_id":
                    {
                        if (func.fixdate != null)
                        {
                            updatedrow = func.fixdate;
                            string contid = updatedrow["contact_id"].ToString();
                            int uprow = int.Parse(contid);
                            uploadfixrow = _original_data.Tables["contacts"].Rows.Find(uprow);
                            uploadfixrow["first_name"] = updatedrow["first_name"].ToString();
                            uploadfixrow["middle_name"] = updatedrow["middle_name"].ToString();
                            uploadfixrow["last_name"] = updatedrow["last_name"].ToString();
                            uploadfixrow["job_title"] = updatedrow["job_title"].ToString();
                            uploadfixrow["company_id"] = updatedrow["company_id"].ToString();
                            uploadfixrow["direct_phone"] = updatedrow["direct_phone"].ToString();
                            uploadfixrow["mobile_phone"] = updatedrow["mobile_phone"].ToString();
                            uploadfixrow["email"] = updatedrow["email"].ToString();
                            uploadfixrow["timestamp"] = updatedrow["timestamp"].ToString();
                            uploadfixrow["user"] = updatedrow["user"].ToString();
                            uploadfixrow.AcceptChanges();
                        }            
                    } break;
                case "company_id":
                    {
                        if (func.fixdate != null)
                        {
                            updatedrow = func.fixdate;
                            string contid = updatedrow["company_id"].ToString();
                            int uprow = int.Parse(contid);
                            uploadfixrow = _original_data.Tables["companies"].Rows.Find(uprow);
                            uploadfixrow["company_name"] = updatedrow["company_name"].ToString();
                            uploadfixrow["street"] = updatedrow["street"].ToString();
                            uploadfixrow["suite"] = updatedrow["suite"].ToString();
                            uploadfixrow["city"] = updatedrow["city"].ToString();
                            uploadfixrow["state"] = updatedrow["state"].ToString();
                            uploadfixrow["zip"] = updatedrow["zip"].ToString();
                            uploadfixrow["main_phone"] = updatedrow["main_phone"].ToString();
                            uploadfixrow["fax"] = updatedrow["fax"].ToString();
                            uploadfixrow["category_id"] = updatedrow["category_id"];
                            uploadfixrow["timestamp"] = updatedrow["timestamp"].ToString();
                            uploadfixrow["user"] = updatedrow["user"].ToString();
                            uploadfixrow.AcceptChanges();
                        }            
                    } break;
            }
            
        }

        public void updatedata(DataTable user)
        {
            string tn = user.Columns[0].ColumnName;
            if (func.update != null || ts == true)
            {                
                if (func.update != null)
                {
                    updatedrow.Delete();
                    updatedrow = func.update;
                }
                else if (ts == true)
                {
                    updatedrow = user.Rows[0];
                }
                switch (tn)
                {
                    case "contact_id":
                        {
                            string contid = updatedrow["contact_id"].ToString();
                            int uprow = int.Parse(contid);
                            uploadfixrow = _original_data.Tables["contacts"].Rows.Find(uprow);
                            uploadfixrow["first_name"] = updatedrow["first_name"].ToString();
                            uploadfixrow["middle_name"] = updatedrow["middle_name"].ToString();
                            uploadfixrow["last_name"] = updatedrow["last_name"].ToString();
                            uploadfixrow["job_title"] = updatedrow["job_title"].ToString();
                            uploadfixrow["company_id"] = updatedrow["company_id"].ToString();
                            uploadfixrow["direct_phone"] = updatedrow["direct_phone"].ToString();
                            uploadfixrow["mobile_phone"] = updatedrow["mobile_phone"].ToString();
                            uploadfixrow["email"] = updatedrow["email"].ToString();
                            uploadfixrow["timestamp"] = DateTime.Now;
                            uploadfixrow["user"] = updatedrow["user"].ToString();
                            if (String.IsNullOrEmpty(uploadfixrow["middle_name"].ToString()))
                            {
                                uploadfixrow["full_name"] = uploadfixrow["first_name"].ToString() + " " + uploadfixrow["last_name"].ToString();
                            }
                            else
                            {
                                uploadfixrow["full_name"] = uploadfixrow["first_name"].ToString() + " " + uploadfixrow["middle_name"].ToString() + " " + uploadfixrow["last_name"].ToString();
                            }
                        } break;
                    case "company_id":
                        {
                            string compid = updatedrow["company_id"].ToString();
                            int uprow = int.Parse(compid);
                            uploadfixrow = _original_data.Tables["companies"].Rows.Find(uprow);
                            uploadfixrow["company_name"] = updatedrow["company_name"].ToString();
                            uploadfixrow["street"] = updatedrow["street"].ToString();
                            uploadfixrow["suite"] = updatedrow["suite"].ToString();
                            uploadfixrow["city"] = updatedrow["city"].ToString();
                            uploadfixrow["state"] = updatedrow["state"].ToString();
                            uploadfixrow["zip"] = updatedrow["zip"].ToString();
                            uploadfixrow["main_phone"] = updatedrow["main_phone"].ToString();
                            uploadfixrow["fax"] = updatedrow["fax"].ToString();
                            uploadfixrow["category_id"] = updatedrow["category_id"].ToString();
                            uploadfixrow["timestamp"] = updatedrow["timestamp"].ToString();
                            uploadfixrow["user"] = updatedrow["user"].ToString();
                        } break;
                }                
            }
        }


        #endregion

        private void mail(string title, Exception ex)
        {
            Mail.mailman.sendmail(string.Format("MySql connection failed to load {0}", title), string.Format("Method call: {0} \r\nError Message: {1} \r\nCall Stack: {2}", ex.TargetSite, ex.Message, ex.StackTrace));
        }        
    }

    #region MySqlException Class
    /// <summary>
    /// Class for returning MySql errors.
    /// </summary>
    public class MySqlException : SystemException
    {
        private string _mess;
        //private MySqlErrorCode _err;
        private Exception _inner;

        /// <summary>
        /// Gets or Sets your return message
        /// </summary>
        public string Message
        {
            get { return _mess; }
            set { _mess = value; }
        }

        /// <summary>
        /// Gets or Sets the Mysql errorcode
        /// </summary>
        //public MySqlErrorCode ErrorCode
        //{
        //    get { return _err; }
        //    set { _err = value; }
        //}

        /// <summary>
        /// Retutns the contianed exception, if any.
        /// </summary>
        public Exception innerException
        {
            get { return _inner; }
        }

        public MySqlException()
        {
        }
        public MySqlException(string Message)
        {
            _mess = Message;
        }
        public MySqlException(string Message, Exception innerException)
        {
            _mess = Message;
            _inner = innerException;
        }
    }
    #endregion

    #region SqlBuilder Class
    public class SqlBuilder
    {

        private DataSet _data;
        private string _sqlstatement;
        private int threads;

        // public event raised when the statement has been constructed
        public delegate void SqlBuilderCompleted(string SqlStatement);
        public event SqlBuilderCompleted BuilderCompleted;

        public SqlBuilder(DataSet DataWithChanges)
        {
            _data = DataWithChanges;
            GC.KeepAlive(_data);
        }

        public SqlBuilder(DataSet DataWithChanges, int Threads)
        {
            _data = DataWithChanges;
            GC.KeepAlive(_data);
            threads = Threads - 1;
        }

        public void Start()
        {
            System.Threading.ManualResetEvent[] threadsdone = new System.Threading.ManualResetEvent[threads];
            SqlWorker[] workers = new SqlWorker[threads];
            SqlWorker worker = null;
            for (int i = 0; i < threads; i++)
            {
                threadsdone[i] = new System.Threading.ManualResetEvent(false);
                switch (i)
                {
                    case 0:
                        if (_data.HasChanges(DataRowState.Added))
                        {
                            worker = new SqlWorker(_data.GetChanges(DataRowState.Added), DataRowState.Added, threadsdone[i]);
                        }
                        break;
                    case 1:
                        if (_data.HasChanges(DataRowState.Deleted))
                        {
                            worker = new SqlWorker(_data.GetChanges(DataRowState.Deleted), DataRowState.Deleted, threadsdone[i]);
                        }
                        break;
                    // invalid enumerator, don't know why
                    //case 2:
                    //    worker = new SqlWorker(_data.GetChanges(DataRowState.Detached), DataRowState.Detached, threadsdone[i]);
                    //    break;
                    case 2:
                        if (_data.HasChanges(DataRowState.Modified))
                        {
                            worker = new SqlWorker(_data.GetChanges(DataRowState.Modified), DataRowState.Modified, threadsdone[i]);
                        }
                        break;
                    case 3:
                        if (_data.HasChanges(DataRowState.Unchanged))
                        {
                            worker = new SqlWorker(_data.GetChanges(DataRowState.Unchanged), DataRowState.Unchanged, threadsdone[i]);
                        }
                        break;
                }

                workers[i] = worker;
                System.Threading.ThreadPool.QueueUserWorkItem(worker.SqlWorkCallback, i);
            }

            // wait until all thread complete
            System.Threading.WaitHandle.WaitAll(threadsdone);
            for (int i = 0; i < threads; i++)
            {
                worker = workers[i];
                _sqlstatement += worker.SqlStatment;

                //raise event
            }
            BuilderCompleted(_sqlstatement);
            _data.Dispose();
        }

        public void StartSave(object[] CommandBuilders)
        {
            threads = CommandBuilders.GetUpperBound(0);
            System.Threading.ManualResetEvent[] threadsdone = new System.Threading.ManualResetEvent[threads];
            SqlWorker[] workers = new SqlWorker[threads];
            SqlWorker worker = null;

            for (int i = 0; i < threads; i++)
            {
                threadsdone[i] = new System.Threading.ManualResetEvent(false);

                worker = new SqlWorker(CommandBuilders[i], _data, threadsdone[i]);

                workers[i] = worker;
                System.Threading.ThreadPool.QueueUserWorkItem(worker.SqlSaveCallback, i);
            }

            // wait until all thread complete
            System.Threading.WaitHandle.WaitAll(threadsdone);
            for (int i = 0; i < threads; i++)
            {
                worker = workers[i];
                _sqlstatement += worker.SqlStatment;

                //raise event
            }
            BuilderCompleted(_sqlstatement);
            _data.Dispose();
        }
    }

    public class SqlWorker
    {
        private DataSet _data;
        private DataTable _table;
        private string _sqlstatement;
        private System.Threading.ManualResetEvent _done;
        private DataRowState _state;
        private Config config;
        private MySqlCommandBuilder _combuild;

        public string SqlStatment { get { return _sqlstatement; } }

        /// <summary>
        /// Default constructor, provide the only the Changed DataSet and the DataRowState used to get those changes
        /// </summary>
        /// <param name="DataChanged"></param>
        public SqlWorker(DataSet DataChanged, DataRowState State, System.Threading.ManualResetEvent DoneEvent)
        {
            _data = DataChanged;
            _done = DoneEvent;
            _state = State;
        }

        public SqlWorker(DataTable DataTableChanged, System.Threading.ManualResetEvent DoneEvent)
        {
            _table = DataTableChanged;
            _done = DoneEvent;
            Stream s = File.Open(Properties.Settings.Default.CONFIGPATHTEST, FileMode.Open);
            BinaryFormatter b = new BinaryFormatter();
            config = (Config)b.Deserialize(s);
            s.Close();
        }

        public SqlWorker(object a, DataSet Data, System.Threading.ManualResetEvent DoneEvent)
        {
            _combuild = (MySqlCommandBuilder)a;
            _done = DoneEvent;
            _data = Data;
        }

        public void SqlWorkCallback(Object context)
        {
            // int threadIndex = (int)context;
            // log "thread processing {1}", threadIndex;
            _sqlstatement = ChangeStates();
            _done.Set();
           // MessageBox.Show(_sqlstatement);
        }

        public void SqlSaveCallback(Object context)
        {
            // int threadIndex = (int)context;
            // log "thread processing {1}", threadIndex;
            MySqlDataAdapter adapter = _combuild.DataAdapter;
            //MySqlCommand com = adapter.SelectCommand;
            //MySqlConnection connector = com.Connection;
   
            //connector.Open();
            

            ////MySqlCommand command = new MySqlCommand(sql, connector);
            ////command.CommandText = string.Format("SELECT {0} FROM contacts", ContactName);

            _combuild.GetUpdateCommand();
            _combuild.GetInsertCommand();
            _combuild.GetDeleteCommand();
            
            //adapter.UpdateCommand = command;
            // need an InsertCommand for new rows
            adapter.Update(_data);

            //connector.Close();
            _done.Set();
            MessageBox.Show("Saved, I think.");
        }

        private string ChangeStates()
        {
            string statement = "";
            foreach (DataTable table in _data.Tables)
            {
                statement += Builder(table);
            }
            return statement;
        }

        private string Builder(DataTable table)
        {
            string statement = "";
            switch (_state)
            {
                case DataRowState.Added:
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        // TODO:  make real SQL statements
                        foreach (DataRow row in table.Rows)
                        {
                            string values = "";
                            foreach (object o in row.ItemArray)
                            {
                                values += string.Format("{0}, ", o);
                            }
                            statement += string.Format("INSERT {0} VALUES {1};", table.TableName, values);
                        }
                    }
                    return statement;
                case DataRowState.Deleted:
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        // TODO:  make real SQL statements
                        statement += string.Format("DELETE {0} WHERE {1};", table.TableName, table.Rows[i].ItemArray[0].ToString());
                    }
                    return statement;
                case DataRowState.Detached:
                    return statement;
                case DataRowState.Modified:
                    for (int i = 0; i < table.Rows.Count; i++)
                    {
                        // TODO:  make real SQL statements
                        foreach (DataRow row in table.Rows)
                        {
                            string values = "";
                            foreach (object o in row.ItemArray)
                            {
                                values += string.Format("{0}, ", o);
                            }
                            statement += string.Format("REPLACE {0} VALUES {1};", table.TableName, values);
                        }
                    }
                    return statement;
                case DataRowState.Unchanged:
                    return statement;
                default:
                    return statement;
            }
        }
    }
    #endregion
}